using System;
using System.Data;
using System.Data.SqlClient;
using Book_Shop.DAL.DAO;

namespace Book_Shop.DAL.Gateway
{
    public class OrderGateway:DbGateway
    {
        InventoryGateway inventoryGateway = new InventoryGateway();
        private OrderDetailsGateway orderDetailsGateway = new OrderDetailsGateway();

        public bool CheckOutBooks(OrderSheet orderSheet)
        {
            Connection.Open();
            using (var transaction = Connection.BeginTransaction())
            {
                
                Command.Transaction = transaction;
                try
                {
                    inventoryGateway.CheckOut(orderSheet);
                    Insert(orderSheet);
                    orderSheet.Id = GetLastInsertedId();
                    orderDetailsGateway.Insert(orderSheet);
                    transaction.Commit();
                    return true;
                }
                catch (Exception exception)
                {
                    transaction.Rollback();
                    throw new Exception("CheckOut Of books could not be performed due to system error!",exception);
                }
                
            }
        }

        private int GetLastInsertedId()
        {
            const string query = "SELECT * FROM OrderMaster ORDER BY Id DESC";
            Command.CommandText = query;
            int lastId = 0;
            using (SqlDataReader dataReader = Command.ExecuteReader())
            {
                while(dataReader.Read())
                {
                    lastId = int.Parse(dataReader["Id"].ToString());
                }
            }
            if (lastId==0)
            {
                throw new Exception("There is no Order Inserted while trying to retrieve a Order");
            }
            else
            {
                return lastId;
            }
            
        }

        private void Insert(OrderSheet orderSheet)
        {
            const string query = "INSERT INTO OrderMaster VALUES(@readerId,@shopId,@salesPersonId,@orderDate,@status,@totalPrice)";
            Command.CommandText = query;
            Command.Parameters.Add("@readerId", SqlDbType.Int);
            Command.Parameters["@readerId"].Value = orderSheet.Reader.Id;
            Command.Parameters.Add("@shopId", SqlDbType.Int);
            Command.Parameters["@shopId"].Value = orderSheet.Shop.Id;
            Command.Parameters.Add("@salesPersonId", SqlDbType.Int);
            Command.Parameters["@salesPersonId"].Value = orderSheet.SalesPerson.Id;
            Command.Parameters.Add("@orderDate", SqlDbType.Date);
            Command.Parameters["@orderDate"].Value = orderSheet.DateOfOrder;
            Command.Parameters.Add("@status", SqlDbType.VarChar);
            Command.Parameters["@status"].Value = orderSheet.Status;
            Command.Parameters.Add("@totalPrice", SqlDbType.Real);
            Command.Parameters["@totalPrice"].Value = orderSheet.TotalPrice;
            Command.ExecuteNonQuery();
        }
        
    }
}